package com.hwtx.form.persistence;

import com.google.common.base.Joiner;
import com.google.common.collect.Lists;
import com.google.common.collect.Maps;
import com.google.common.collect.Sets;
import com.hwtx.form.domain.FormConstants;
import com.hwtx.form.domain.def.FormConfigExt;
import com.hwtx.form.domain.def.meta.config.FormComponentConfig;
import com.hwtx.form.domain.handle.SelectItemsInfo;
import com.hwtx.form.domain.handle.core.QueryConfigInfo;
import com.hwtx.form.domain.handle.core.SqlBuilderElement;
import com.hwtx.form.domain.handle.datamodel.DataModel;
import com.hwtx.form.domain.handle.datamodel.DataModelDetail;
import com.hwtx.form.domain.handle.datamodel.DataModelRelate;
import com.hwtx.form.domain.permission.DataQueryInfo;
import com.hwtx.form.persistence.ds.DatasourceDao;
import com.hwtx.form.persistence.ds.DriverAdapter;
import com.hwtx.form.persistence.ds.metadata.Column;
import com.hwtx.form.persistence.ds.metadata.Constraint;
import com.hwtx.form.persistence.ds.metadata.Table;
import com.hwtx.form.util.*;
import io.geekidea.boot.framework.exception.BusinessException;
import jakarta.annotation.Resource;
import lombok.SneakyThrows;
import lombok.extern.slf4j.Slf4j;
import net.sf.jsqlparser.JSQLParserException;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.collections4.MapUtils;
import org.apache.commons.lang3.BooleanUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.commons.lang3.tuple.Pair;
import org.springframework.stereotype.Repository;

import java.util.*;
import java.util.concurrent.atomic.AtomicBoolean;
import java.util.concurrent.atomic.AtomicInteger;
import java.util.stream.Collectors;
import java.util.stream.IntStream;

import static com.hwtx.form.domain.FormConstants.DEFAULT_FIELDS;
import static com.hwtx.form.domain.FormConstants.DefaultColumn.getCreateDefaultColumns;
import static com.hwtx.form.domain.FormConstants.DefaultColumn.getModifyDefaultColumns;

@Repository
@Slf4j
public class DataSourceRepo {

    @Resource
    private DatasourceDao datasourceDao;

    public static Pair<String, List<Object>> getSqlAndParam(QueryParserResult result, List<Map<String, Collection<Object>>> requestParam, int paramSize, String itemOp) {
        List<Object> sqlParams = Lists.newArrayList();
        SQL sql = new SQL();
        buildSelect(sql, result);
        StringBuilder builder = buildFilter(requestParam, result, itemOp, sqlParams, paramSize);
        if (builder != null) {
            sql.WHERE(builder.toString());
        }
        if (result.getGroupBy() != null) {
            result.getGroupBy().getItems().forEach(item -> {
                if (item.isVariable()) {
                    requestParam.forEach(param -> {
                        Collection<Object> values = param.get(item.getValue().toString());
                        if (CollectionUtils.isNotEmpty(values)) {
                            sql.GROUP_BY(values.iterator().next().toString());
                        }
                    });
                } else {
                    sql.GROUP_BY(item.getValue().toString());
                }
            });
        }
        if (result.getOrderBy() != null) {
            result.getOrderBy().getItems().forEach(item -> {
                if (item.isVariable()) {
                    requestParam.forEach(param -> {
                        Collection<Object> values = param.get(item.getValue().toString());
                        if (CollectionUtils.isNotEmpty(values)) {
                            sql.ORDER_BY(values.iterator().next().toString() + " " + (item.isAsc() ? "asc" : "desc"));
                        }
                    });
                } else {
                    sql.ORDER_BY(item.getValue().getValue().toString() + " " + (item.isAsc() ? "asc" : "desc"));
                }
            });
        }
        return Pair.of(sql.toString(), sqlParams);
    }

    private static void buildSelect(SQL sql, QueryParserResult result) {
        boolean distinct = BooleanUtils.toBoolean(result.getProject().getDistinct());
        result.getProject().getColumns().forEach(column -> {
            String columnInfo = (column.getTable() != null ? column.getTable() + "." + column.getItem() : column.getItem()) + (StringUtils.isNotEmpty(column.getAlias()) ? " as " + column.getAlias() : "");
            if (distinct) {
                sql.SELECT_DISTINCT(columnInfo);
            } else {
                sql.SELECT(columnInfo);
            }
        });
        sql.FROM(result.getTable().getName());
    }

    private static int travelFilter(BinaryNode<QueryParserResult.Filter> node, Map<String, Collection<Object>> values, StringBuilder builder, Map<String, Object> paramValues, AtomicInteger count) {
        if (node == null || node.getData() == null) {
            return -1;
        }
        for (String item : node.getData().getVars()) {
            Object value = values.get(item);
            if (value == null || StringUtils.isEmpty(value.toString())) {
                count.getAndIncrement();
                return -1;
            }
            paramValues.put(item, value);
        }
        boolean binaryFilter = false;
        if (node.getData().getClass() != QueryParserResult.BinaryFilter.class) {
            builder.append(node.getData().getExpress());
        } else {
            binaryFilter = true;
        }
        if (node.getLeft() == null && node.getRight() == null) {
            return 2;
        }

        boolean leftAdd = false;
        if (node.getLeft() != null) {
            StringBuilder leftBuilder = new StringBuilder();
            int ret = travelFilter(node.getLeft(), values, leftBuilder, paramValues, count);
            if (ret > 0) {
                if (node.getRight() != null) {
                    builder.append("(");
                }
                builder.append(leftBuilder);
                leftAdd = true;
            }
        }
        if (node.getRight() != null) {
            StringBuilder rightBuilder = new StringBuilder();
            int ret = travelFilter(node.getRight(), values, rightBuilder, paramValues, count);
            if (ret > 0 && binaryFilter && leftAdd) {
                builder.append(" ").append(((QueryParserResult.BinaryFilter) node.getData()).getOp()).append(" ");
                builder.append(rightBuilder);
            }
            if (leftAdd) {
                builder.append(")");
            }
        }
        return 1;
    }

    public static void main(String[] args) throws JSQLParserException {
        String sql = "select distinct merchant_code as c from mall_merchant_category where cat1 in (@cat1) and cat2 in (@cat2)";
        QueryParserResult result = QueryParser.parser(sql);
//        Map<String, Object> param = Map.of("cat1", List.of(1, 2, 3), "cat2", List.of(1));
//        System.out.println(getSqlAndParam(result, param, 10));
        List<Map<String, Collection<Object>>> param1 = List.of(Maps.newLinkedHashMap(Map.of("cat1", List.of(1, 2, 3), "cat2", List.of(1))), Maps.newLinkedHashMap(Map.of("cat1", List.of(4, 5, 6, 7), "cat2", List.of(4, 5))));
        System.out.println(getSqlAndParam(result, param1, 10, "AND"));
    }

    private static StringBuilder buildFilter(List<Map<String, Collection<Object>>> params, QueryParserResult result, String itemOp, List<Object> sqlParams, int paramSize) {
        AtomicInteger count = new AtomicInteger(0);
        StringBuilder builder = new StringBuilder();
        params.forEach(param -> {
            StringBuilder itemBuilder = new StringBuilder();
            Map<String, Object> paramValues = Maps.newLinkedHashMap();
            travelFilter(result.getFilterRoot(), param, itemBuilder, paramValues, count);
            if (!itemBuilder.isEmpty()) {
                String filter = itemBuilder.toString();
                for (Map.Entry<String, Object> entry : paramValues.entrySet()) {
                    String key = entry.getKey();
                    Object value = entry.getValue();
                    if (!filter.contains("@" + key)) {
                        continue;
                    }
                    if (value instanceof Collection<?>) {
                        filter = filter.replace("@" + key, ((Collection<?>) value).stream().map(v -> "?").collect(Collectors.joining(",")));
                        sqlParams.addAll((Collection<?>) value);
                    } else {
                        filter = filter.replace("@" + key, "?");
                        sqlParams.add(value);
                    }
                }
                builder.append(filter).append(" ").append(itemOp).append(" ");
            }
        });
        builder.delete(builder.length() - itemOp.length() - 1, builder.length());
        if (count.get() == paramSize) {
            return null;
        }
        return builder;
    }

    public boolean exist(String name) {
        return datasourceDao.exists(getTable(name));
    }

    public Map<String, String> getTableAndComments() {
        return datasourceDao.getTableAndComment();
    }

    private List<Constraint> getConstraints(String tableName) {
        Table table = getTable(tableName);
        try {
            return datasourceDao.constraints(table);
        } catch (Exception e) {
            log.error("", e);
        }
        return Lists.newArrayList();
    }

    public void add(Constraint constraint) {
        try {
            datasourceDao.add(constraint);
        } catch (Exception e) {
            log.error("", e);
        }
    }

    public void drop(Constraint constraint) {
        try {
            datasourceDao.drop(constraint);
        } catch (Exception e) {
            log.error("", e);
        }
    }

    public List<String> getConstraintColumns(String tableName) {
        return getConstraints(tableName).stream().filter(constraint -> Objects.equals(constraint.getType(), Constraint.TYPE.UNIQUE)).map(constraint -> constraint.getColumns().keySet()).flatMap(Set::stream).toList();
    }

    public Map<String, Column> getColumns(String tableName) {
        Table table = getTable(tableName);
        try {
            return datasourceDao.columns(table);
        } catch (Exception e) {
            log.error("", e);
        }
        return Maps.newHashMap();
    }

    public Set<String> getColumnNames(String tableName) {
        return getColumns(tableName).keySet();
    }

    public String create(String formName, List<DataModelDetail> details) {
        String tableName = getTableName(formName);
        Table table = new Table(tableName);
        if (datasourceDao.exists(table)) {
            log.info("数据表【{}】已存在无需创建", tableName);
            return "同步数据库表成功";
        }
        details.forEach(config -> table.addColumn(buildColumn(config)));
        try {
            datasourceDao.create(table);
        } catch (Exception e) {
            log.error("", e);
            throw new RuntimeException("创建表单数据表失败");
        }
        return "同步数据库表成功";
    }

    public void addColumn(String tableName, DataModelDetail detail) {
        Table table = new Table(getTableName(tableName));
        try {
            datasourceDao.alter(table, buildColumn(detail));
        } catch (Exception e) {
            log.error("", e);
            throw new RuntimeException("新增表单字段列失败");
        }
    }

    @SneakyThrows
    public void removeColumns(String tableName, Set<String> removedCollections, boolean forceDel) {
        tableName = getTableName(tableName);
        Table table = new Table(tableName);
        if (!datasourceDao.exists(table)) {
            log.error("数据表【{}】不存在，无法删除列【{}】", tableName, removedCollections);
            return;
        }
        datasourceDao.checkSchema(table);
        Integer count = datasourceDao.runtime().getProcessor().queryForObject("select count(1) from " + table.getCatalogName() + "." + tableName, Integer.class);
        if (count != null && count > 1 && !forceDel) {
            log.error("数据表【{}】已存在数据，无法删除列【{}】", tableName, removedCollections);
            return;
        }
        removedCollections.forEach(col -> {
            try {
                Column column = new Column(col);
                column.setDrop(true);
                datasourceDao.alter(table, column);
            } catch (Exception e) {
                log.error("", e);
                throw new RuntimeException("删除表单字段列失败");
            }
        });
    }

    public void updateColumns(String tableName, Set<DataModelDetail> details) {
        Table table = new Table(getTableName(tableName));
        try {
            for (DataModelDetail dataModelEntity : details) {
                datasourceDao.alter(table, buildColumn(dataModelEntity));
            }
        } catch (Exception e) {
            log.error("", e);
            throw new RuntimeException("新增表单字段列失败");
        }
    }

    private Column buildColumn(DataModelDetail config) {
        Column column = new Column();
        column.setName(config.getName()).setTypeName(config.getType().getName().toUpperCase());
        if (config.getLength() != null) {
            column.setPrecision(config.getLength());
        }
        if (config.getPoints() != null) {
            column.setScale(config.getPoints());
        }
        column.setComment(config.getDescription());
        DriverAdapter.SQL_BUILD_IN_VALUE defValue = getDefValue(config.getDefValue());
        if (defValue != null) {
            column.setDefaultValue(defValue);
        } else {
            column.setDefaultValue(config.getDefValue());
        }
        if (config.getNotNull() != null) {
            column.setNullable(config.getNotNull() == 1 ? 0 : 1);
        }
        if (config.getPriKey() != null) {
            column.setPrimary(config.getPriKey() == 1 ? 1 : 0);
        }
        if (config.getAfterFieldName() != null) {
            column.setAfter(config.getAfterFieldName());
        }
        if (DEFAULT_FIELDS.containsKey(config.getName()) && Objects.equals(config.getPriKey(), (byte) 1)) {
            column.setAutoIncrement(true);
        }
        return column;
    }

    private String getTableName(String name) {
        return name;
    }

    public String buildSelectList(DataModel dataModel, List<DataModelRelate> relates, Map<String, List<SelectItemsInfo.SelectItemInfo>> tableItems, List<QueryConfigInfo.RequestParam> filterExtraItems, List<FormConfigExt.SearchConfig> searchConfigs, List<DataQueryInfo.DataModelFilterInfo> dataModelFilters,
                                  List<QueryConfigInfo.OrGroupParam> orGroupParams, SqlBuilderElement.OrderInfo orderInfo, boolean isPage, Map<String, Object> whereItemAndValues, FilterCallback callback) {
        SQL sql = new SQL();
        Map<String, String> tenantsTableAndField = Maps.newHashMap();
        Map<String, String> tableAndAlias = Maps.newHashMap();
        if (CollectionUtils.isEmpty(relates)) {
            List<SelectItemsInfo.SelectItemInfo> items = tableItems.get(dataModel.getMaster());
            if (CollectionUtils.isEmpty(items)) {
                throw new BusinessException("查询项不可为空");
            }
            Map<String, String> selectItems = Maps.newHashMap();
            items.forEach(item -> {
                if (StringUtils.isEmpty(item.getField())) {
                    throw new BusinessException("查询字段【" + item.getField() + "】字段名称不可为空");
                }
                selectItems.put(item.getField(), item.getName());
            });
            buildSelectItems(selectItems, sql);
            String table = getTableName(dataModel.getMaster());
            sql.FROM(table);
            tableAndAlias.put(table, "");
            if (callback != null) {
                tenantsTableAndField.put(table, callback.getTenantsField(table));
            }
        } else {
            tableAndAlias.putAll(buildJoinItemsAndOnInfo(dataModel.getMaster(), sql, relates, tableItems, tenantsTableAndField, callback));
        }
        buildFilter(filterExtraItems, searchConfigs, tableAndAlias, whereItemAndValues, sql, dataModelFilters, orGroupParams, tenantsTableAndField);
        if (!orderInfo.empty()) {
            sql.ORDER_BY(orderInfo.getOrderBy() + " " + orderInfo.getOrderDir());
        }
        if (isPage) {
            sql.LIMIT("?").OFFSET("?");
        }
        return sql.toString();
    }

    public String buildCount(DataModel dataModel, List<DataModelRelate> relates,
                             List<QueryConfigInfo.RequestParam> filterExtraItems,
                             List<FormConfigExt.SearchConfig> searchConfigs,
                             List<DataQueryInfo.DataModelFilterInfo> dataModelFilters,
                             List<QueryConfigInfo.OrGroupParam> orGroupParams, Map<String, Object> whereItemAndValues, FilterCallback callback) {
        SQL sql = new SQL();
        Map<String, String> tenantsTableAndField = Maps.newHashMap();
        sql.SELECT("COUNT(*) ");
        Map<String, String> tableAndAlias = Maps.newHashMap();
        if (CollectionUtils.isEmpty(relates)) {
            String table = getTableName(dataModel.getMaster());
            sql.FROM(table);
            tableAndAlias.put(table, "");
            if (callback != null) {
                tenantsTableAndField.put(table, callback.getTenantsField(table));
            }
        } else {
            List<String> hasHandleTables = Lists.newArrayList();
            AtomicInteger index = new AtomicInteger(1);
            relates.forEach(relate -> {
                if (!hasHandleTables.contains(relate.getFirstName())) {
                    tableAndAlias.put(relate.getFirstName(), "t" + index.getAndIncrement());
                    if (callback != null) {
                        tenantsTableAndField.put(relate.getFirstName(), callback.getTenantsField(relate.getFirstName()));
                    }
                    hasHandleTables.add(relate.getFirstName());
                }
                if (!hasHandleTables.contains(relate.getSecondName())) {
                    tableAndAlias.put(relate.getSecondName(), "t" + index.getAndIncrement());
                    if (callback != null) {
                        tenantsTableAndField.put(relate.getSecondName(), callback.getTenantsField(relate.getSecondName()));
                    }
                    hasHandleTables.add(relate.getSecondName());
                }
                buildJoinInfo(sql, tableAndAlias, JacksonUtil.json2Array(relate.getJoinFields()), relate, index, FormConstants.JoinType.of(relate.getJoinType()));
            });
        }
        buildFilter(filterExtraItems, searchConfigs, tableAndAlias, whereItemAndValues, sql, dataModelFilters, orGroupParams, tenantsTableAndField);
        return sql.toString();
    }

    private void buildFilter(List<QueryConfigInfo.RequestParam> filterExtraItems, List<FormConfigExt.SearchConfig> searchConfigs, Map<String, String> tableAndAlias,
                             Map<String, Object> whereItemAndValues, SQL sql,
                             List<DataQueryInfo.DataModelFilterInfo> dataModelFilters,
                             List<QueryConfigInfo.OrGroupParam> orGroupParams, Map<String, String> tenantsTableAndField) {
        sql.WHERE("1 = 1");
        searchConfigs.forEach(searchConfig -> {
            if (MapUtils.isNotEmpty(searchConfig.getAddFields())) {
                tableAndAlias.forEach((table, alias) -> {
                    List<String> fields = searchConfig.getAddFields().get(table);
                    if (CollectionUtils.isEmpty(fields)) {
                        return;
                    }
                    fields.forEach(field -> sql.WHERE(StringUtils.isEmpty(alias) ? field : alias + "." + field + " " + searchConfig.getOperator() + " ?"));
                });
            } else {
                sql.WHERE(searchConfig.getProperty() + " " + searchConfig.getOperator() + " ?");
            }
        });
        filterExtraItems.forEach(item -> {
            if (tenantsTableAndField.containsValue(item.getParamName())) {
                return;
            }
            if (item.isVariable()) {
                if (FormUtil.getStrPostValue(whereItemAndValues, item.getParamName()) != null) {
                    sql.WHERE(item.getProperty() + " " + item.getOperator() + " ?");
                }
            } else {
                sql.WHERE(item.getProperty() + " " + item.getOperator() + " " + item.getValue());
            }
        });
        if (MapUtils.isNotEmpty(tenantsTableAndField)) {
            AtomicBoolean hasTenants = new AtomicBoolean(false);
            tenantsTableAndField.forEach((table, field) -> {
                if (field == null) {
                    return;
                }
                sql.WHERE((StringUtils.isNotEmpty(tableAndAlias.get(table)) ? tableAndAlias.get(table) + "." + field : field) + " = ?");
                hasTenants.set(true);
            });
            if (!hasTenants.get()) {
                throw new BusinessException("请配置租户字段");
            }
        }
        if (CollectionUtils.isNotEmpty(orGroupParams)) {
            StringBuilder sb = new StringBuilder();
            orGroupParams.stream().filter(orgGroupParam -> CollectionUtils.isNotEmpty(orgGroupParam.getGroups())).forEach(orgGroupParam -> {
                AtomicInteger index = new AtomicInteger(1);
                int size = orgGroupParam.getGroups().size();
                orgGroupParam.getGroups().forEach(group -> {
                    group.forEach((k, v) -> {
                        if (v.size() == 1) {
                            sb.append(k).append(" ").append(FormConstants.OperatorType.EQ.getSymbol()).append(" ").append(v.iterator().next());
                        } else {
                            sb.append(k).append(" ").append(FormConstants.OperatorType.IN.getSymbol()).append(" (").append(Joiner.on(",").join(v)).append(")");
                        }
                        sb.append(" AND ");
                    });
                    sb.delete(sb.length() - 4, sb.length());
                    if (index.getAndIncrement() != size) {
                        sb.append(" OR ");
                    }
                });
            });
            sql.WHERE("(" + sb + ")");
        }
        dataModelFilters.forEach(filter -> {
            String table = filter.getTable();
            String alias = null;
            if (StringUtils.isNotEmpty(table)) {
                alias = tableAndAlias.get(table);
            }
            FormComponentConfig.ParamMapping paramMapping = filter.getOutputField();
            String field = paramMapping.getAlias();
            if (StringUtils.isEmpty(field)) {
                field = paramMapping.getKey();
            }
            sql.WHERE(StringUtils.isNoneBlank(alias) ? alias + "." + field : field + " in (" + filter.getFinalSql() + " )");
        });
    }

    public String buildUpdateSql(String table, List<String> updateItems, List<String> filterItems, boolean hasDefaultColumns) {
        StringBuilder builder = new StringBuilder();
        builder.append("UPDATE ").append(getTableName(table)).append(" SET ");
        updateItems.forEach(item -> builder.append(item).append(" = ?").append(", "));
        if (hasDefaultColumns) {
            getModifyDefaultColumns().forEach((column, value) -> builder.append(column).append(" = ?").append(", "));
        }
        builder.delete(builder.length() - 2, builder.length());
        builder.append(" WHERE 1=1 ");
        filterItems.forEach(item -> builder.append(" AND ").append(item).append(" = ?"));
        return builder.toString();
    }

    public String buildSelectSql(String table, Map<String, String> selectItems, List<String> filterItems) {
        SQL sql = new SQL();
        buildSelectItems(selectItems, sql);
        sql.FROM(getTableName(table)).WHERE("1=1");
        filterItems.forEach(item -> sql.WHERE(item + " = ?"));
        return sql.toString();
    }

    private void buildSelectItems(Map<String, String> items, SQL sql) {
        sql.SELECT(FormConstants.DefaultColumn.id.name());
        items.entrySet().stream().filter(item -> !Objects.equals(item.getKey(), FormConstants.DefaultColumn.id.name())).forEach(item -> {
            if (StringUtils.isNotBlank(item.getValue())) {
                sql.SELECT(item.getKey() + " as " + item.getValue());
            } else {
                sql.SELECT(item.getKey());
            }
        });
    }

    public List<Pair<String, String>> buildBulkDelete(DataModel dataModel, List<DataModelRelate> relates, String filterItem, int valueSize) {
        if (CollectionUtils.isEmpty(relates)) {
            SQL sql = new SQL();
            String table = getTableName(dataModel.getMaster());
            sql.DELETE_FROM(table);
            sql.WHERE("1 = 1");
            buildInFilter(filterItem, valueSize, sql, null);
            return List.of(Pair.of(table, sql.toString()));
        } else {
            Set<String> hasHandleTables = Sets.newHashSet();
            List<Pair<String, String>> relateSqls = Lists.newArrayList();
            relates.forEach(relate -> {
                hasHandleTables.add(getTableName(relate.getFirstName()));
                hasHandleTables.add(getTableName(relate.getSecondName()));
            });
            hasHandleTables.forEach(table -> {
                SQL sql = new SQL();
                sql.DELETE_FROM(table);
                sql.WHERE("1 = 1");
                buildInFilter(filterItem, valueSize, sql, null);
                relateSqls.add(Pair.of(table, sql.toString()));
            });
            return relateSqls;
        }
    }

    public String buildSelectByIds(DataModel dataModel, List<DataModelRelate> relates, String filterItem, int valueSize) {
        SQL sql = new SQL();
        Map<String, String> tableAndAlias = Maps.newHashMap();
        if (CollectionUtils.isEmpty(relates)) {
            String table = getTableName(dataModel.getMaster());
            sql.SELECT("*").FROM(table);
            tableAndAlias.put(table, "");
        } else {
            tableAndAlias = buildJoinItemsAndOnInfo(dataModel.getMaster(), sql, relates, null, null, null);
            tableAndAlias.forEach((table, alias) -> sql.SELECT(alias + "." + FormConstants.DefaultColumn.id.name() + getAlias(FormConstants.DefaultColumn.id.name(), null, table)));
        }
        sql.WHERE("1=1");
        String master = dataModel.getMaster();
        buildInFilter(filterItem, valueSize, sql, tableAndAlias.get(master));
        return sql.toString();
    }

    private void buildInFilter(String filterItem, int valueSize, SQL sql, String inTableAlias) {
        StringBuilder condition = new StringBuilder();
        condition.append(StringUtils.isNoneBlank(inTableAlias) ? inTableAlias + "." + filterItem : filterItem);
        condition.append(" in (");
        IntStream.range(0, valueSize).forEach(item -> condition.append("?").append(", "));
        condition.delete(condition.length() - 2, condition.length());
        condition.append(")");
        sql.WHERE(condition.toString());
    }

    public String buildInsertSql(String target, Collection<String> insertItems, boolean addDefaultColumns) {
        StringBuilder sb = new StringBuilder();
        String table;
        table = getTableName(target);
        sb.append("INSERT INTO ").append(table).append("(");
        insertItems.forEach(item -> sb.append(item).append(", "));
        if (addDefaultColumns) {
            for (String item : getCreateDefaultColumns().keySet()) {
                sb.append(item).append(", ");
            }
        }
        sb.delete(sb.length() - 2, sb.length());
        sb.append(") VALUES (");
        insertItems.forEach(item -> sb.append("?").append(", "));
        if (addDefaultColumns) {
            getCreateDefaultColumns().keySet().forEach(item -> sb.append("?").append(", "));
        }
        sb.delete(sb.length() - 2, sb.length());
        sb.append(")");
        return sb.toString();
    }

    private Table getTable(String name) {
        String tableName = getTableName(name);
        return new Table(tableName);
    }

    private DriverAdapter.SQL_BUILD_IN_VALUE getDefValue(String value) {
        return Arrays.stream(DriverAdapter.SQL_BUILD_IN_VALUE.values()).filter(v -> v.name().equalsIgnoreCase(value)).findFirst().orElse(null);
    }

    public String buildSelect(DataModel dataModel, List<DataModelRelate> relates, Map<String, String> filterItems, Map<String, Set<String>> tableColumns) {
        SQL sql = new SQL();
        Map<String, String> tableAndAlias = Maps.newHashMap();
        if (CollectionUtils.isEmpty(relates)) {
            sql.SELECT(FormConstants.DefaultColumn.id.name());
            sql.FROM(getTableName(dataModel.getMaster()));
            tableAndAlias.put(getTableName(dataModel.getMaster()), null);
        } else {
            AtomicInteger index = new AtomicInteger(1);
            String name = getTableName(dataModel.getMaster());
            tableAndAlias.put(name, "t" + index.getAndIncrement());
            AtomicBoolean hasHandleSelect = new AtomicBoolean(false);
            if (tableColumns.get(dataModel.getMaster()).contains(FormConstants.DefaultColumn.id.name())) {
                sql.SELECT(tableAndAlias.get(name) + "." + FormConstants.DefaultColumn.id.name());
                hasHandleSelect.set(true);
            }
            relates.forEach(relate -> {
                String joinType = relate.getJoinType();
                if (StringUtils.isEmpty(relate.getJoinFields())) {
                    throw new BusinessException("多表关联字段不能为空，关联表：" + relate.getSecondName() + "," + relate.getFirstName());
                }
                List<Map<String, String>> fields = JacksonUtil.json2Array(relate.getJoinFields());
                if (!hasHandleSelect.get()) {
                    if (tableColumns.get(relate.getSecondName()).contains(FormConstants.DefaultColumn.id.name())) {
                        tableAndAlias.put(relate.getSecondName(), "t" + index.getAndIncrement());
                        sql.SELECT(tableAndAlias.get(relate.getSecondName()) + "." + FormConstants.DefaultColumn.id.name());
                        hasHandleSelect.set(true);
                    }
                }
                buildJoinInfo(sql, tableAndAlias, fields, relate, index, FormConstants.JoinType.of(joinType));
            });
        }
        sql.WHERE("1=1");
        filterItems.forEach((table, item) -> sql.WHERE(tableAndAlias.get(table) != null ? tableAndAlias.get(table) + "." + item + " = ?" : item + " = ?"));
        if (StringUtils.isNotBlank(dataModel.getOuterFilter())) {
            sql.WHERE(dataModel.getOuterFilter());
        }
        return sql.toString();
    }

    public void buildJoinInfo(SQL sql, Map<String, String> tableAndAlias, List<Map<String, String>> fields, DataModelRelate relate, AtomicInteger index, FormConstants.JoinType joinType) {
        String t = getTableName(relate.getSecondName());
        if (!tableAndAlias.containsKey(t)) {
            tableAndAlias.put(t, "t" + index.getAndIncrement());
        }
        String firstAlias = tableAndAlias.get(relate.getFirstName());
        String secondAlias = tableAndAlias.get(relate.getSecondName());
        sql.FROM(getTableName(relate.getFirstName()) + " as " + firstAlias);
        if (CollectionUtils.isNotEmpty(fields)) {
            fields.forEach(field -> {
                String join = t + " as " + tableAndAlias.get(t) + " ON " + firstAlias + "." + field.get("firstField") + " = " + secondAlias + "." + field.get("secondField");
                switch (joinType) {
                    case JOIN:
                        sql.JOIN(join);
                        break;
                    case LEFT_JOIN:
                        sql.LEFT_OUTER_JOIN(join);
                        break;
                    case RIGHT_JOIN:
                        sql.RIGHT_OUTER_JOIN(join);
                        break;
                    case null:
                }
            });
        }
    }

    public String buildRelateSql(DataModel dataModel, List<DataModelRelate> relates, Map<String, List<SelectItemsInfo.SelectItemInfo>> tableItems, Map<String, Map<String, String>> extraColumns, Collection<String> filterItems, Map<String, List<String>> fieldsAndTables) {
        SQL sql = new SQL();
        Map<String, String> tableAndAlias = buildJoinItemsAndOnInfo(dataModel.getMaster(), sql, relates, tableItems, null, null);
        if (MapUtils.isNotEmpty(extraColumns)) {
            extraColumns.forEach((table, columns) -> columns.forEach((key, value) -> sql.SELECT(tableAndAlias.get(table) + "." + key + (StringUtils.isNotBlank(value) ? " AS " + value : ""))));
        }
        sql.WHERE("1=1");
        filterItems.forEach(item -> {
            List<String> tables = fieldsAndTables.get(item);
            if (CollectionUtils.isEmpty(tables)) {
                throw new BusinessException("多表关联字段不能为空，关联字段：" + item);
            }
            String t = tables.getFirst();
            if (tables.size() > 1) {
                t = dataModel.getMaster();
            }
            sql.WHERE(tableAndAlias.get(t) + "." + item + " = ?");
        });
        if (StringUtils.isNotBlank(dataModel.getOuterFilter())) {
            sql.WHERE(dataModel.getOuterFilter());
        }
        return sql.toString();
    }

    private Map<String, String> buildJoinItemsAndOnInfo(String master, SQL sql, List<DataModelRelate> relates, Map<String, List<SelectItemsInfo.SelectItemInfo>> tableItems, Map<String, String> tenantsTableAndField, FilterCallback callback) {
        List<String> hasHandleTables = Lists.newArrayList();
        AtomicInteger index = new AtomicInteger(1);
        Map<String, String> tableAndAlias = Maps.newHashMap();
        relates.forEach(relate -> {
            if (!hasHandleTables.contains(relate.getFirstName())) {
                if (tableItems == null) {
                    tableAndAlias.put(relate.getFirstName(), "t" + index.getAndIncrement());
                } else {
                    List<SelectItemsInfo.SelectItemInfo> selectItems = tableItems.get(relate.getFirstName());
                    if (CollectionUtils.isNotEmpty(selectItems)) {
                        tableAndAlias.put(relate.getFirstName(), "t" + index.getAndIncrement());
                        selectItems.forEach(item -> sql.SELECT(tableAndAlias.get(relate.getFirstName()) + "." + item.getField() + getAlias(item.getField(), item.getName(), relate.getFirstName())));
                    }
                }
                if (tenantsTableAndField != null && callback != null) {
                    tenantsTableAndField.put(relate.getFirstName(), callback.getTenantsField(relate.getFirstName()));
                }
                hasHandleTables.add(relate.getFirstName());
            }

            if (!hasHandleTables.contains(relate.getSecondName())) {
                if (tableItems == null) {
                    tableAndAlias.put(relate.getSecondName(), "t" + index.getAndIncrement());
                } else {
                    List<SelectItemsInfo.SelectItemInfo> selectItems = tableItems.get(relate.getSecondName());
                    if (CollectionUtils.isNotEmpty(selectItems)) {
                        tableAndAlias.put(relate.getSecondName(), "t" + index.getAndIncrement());
                        selectItems.forEach(item -> sql.SELECT(tableAndAlias.get(relate.getSecondName()) + "." + item.getField() + getAlias(item.getField(), item.getName(), relate.getSecondName())));
                    }
                }
                if (tenantsTableAndField != null && callback != null) {
                    tenantsTableAndField.put(relate.getSecondName(), callback.getTenantsField(relate.getSecondName()));
                }
                hasHandleTables.add(relate.getSecondName());
            }
            buildJoinInfo(sql, tableAndAlias, JacksonUtil.json2Array(relate.getJoinFields()), relate, index, FormConstants.JoinType.of(relate.getJoinType()));
        });
        sql.SELECT(tableAndAlias.get(master) + "." + FormConstants.DefaultColumn.id.name());
        return tableAndAlias;
    }

    private String getAlias(String field, String alias, String table) {
        if (field.equals(FormConstants.DefaultColumn.id.name())) {
            return " AS " + table + "_" + field;
        }
        return (StringUtils.isNotBlank(alias) ? " AS " + alias : "");
    }

    public List<String> getTables() {
        return datasourceDao.getTables();
    }

    public boolean hasData(String table) {
        Integer count = datasourceDao.runtime().getProcessor().queryForObject("select count(1) from " + table, Integer.class);
        return count != null && count > 0;
    }

    public interface FilterCallback {
        String getTenantsField(String table);
    }

}
